package com.tudouji.common.utils.poi;


import com.tudouji.common.utils.file.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

public class POIUtils {
    private final static String XLS = "xls";
    private final static String XLSX = "xlsx";

    public static List<String[]> readExcel(String fileName) throws IOException {
        // 检查文件
        checkFile(fileName);
        //获得Workbook工作薄对象
        Workbook workbook = getWorkBook(fileName);
        //创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回
        List<String[]> list = new ArrayList<String[]>();
        if (null != workbook) {
            for (int sheetNum  = 0; sheetNum < workbook.getNumberOfSheets();sheetNum++) {
                //获得当前sheet工作表
                Sheet sheet = workbook.getSheetAt(sheetNum);
                if (null == sheet) {
                    continue;
                }
                //获得当前sheet的开始行
                readSheet(list, sheet);
            }
            workbook.close();
        }
        return list;

    }

    public static List<String[]> readExcelSheet(String fileName, int sheetNum) throws IOException {
        // 检查文件
        checkFile(fileName);
        //获得Workbook工作薄对象
        Workbook workbook = getWorkBook(fileName);
        //创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回
        List<String[]> list = new ArrayList<String[]>();
        if (null != workbook) {
                //获得当前sheet工作表
                Sheet sheet = workbook.getSheetAt(sheetNum);
                if (null == sheet) {
                    return null;
                }
            readSheet(list, sheet);
        }
            workbook.close();
        return list;
    }

    private static void readSheet(List<String[]> list, Sheet sheet) {
        //获得当前sheet的开始行
        int firstRowNum = sheet.getFirstRowNum();
        //获得当前sheet的结束行
        int lastRowNum = sheet.getLastRowNum();
        //循环除了第一行的所有行
        for (int rowNum = firstRowNum + 1; rowNum <= lastRowNum; rowNum++) {
            //获得当前行
            Row row = sheet.getRow(rowNum);
            if (row == null) {
                continue;
            }
            //获得当前行的开始列
            int firstCellNum = row.getFirstCellNum();
            //获得当前行的列数
            int lastCellNum = row.getPhysicalNumberOfCells();
            String[] cells = new String[lastCellNum];

            //循环当前行
            for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++) {
                Cell cell = row.getCell(cellNum);
                cells[cellNum] = getCellValue(cell);
            }

            list.add(cells);
        }
    }

    /**
     * 获取当前行数据
     * @param cell
     * @return
     */
    @SuppressWarnings("deprecation")
    private static String getCellValue(Cell cell) {
        String cellValue = "";
        if(cell == null){
            return cellValue;
        }
        //把数字当成String来读，避免出现1读成1.0的情况
        if(cell.getCellType() == CellType.NUMERIC){
            cell.setCellType(CellType.STRING);
        }
        //判断数据的类型
        switch (cell.getCellType()){
            case NUMERIC: //数字
//                cellValue = String.valueOf(cell.getNumericCellValue());
//                break;
            case FORMULA: //公式
                cellValue = formatNumber(cell);
//                System.out.println("~~~" + cell.getCellStyle().getDataFormatString() + "!!!!!!!!" + cell.getNumericCellValue() + "---+"+ formatNumber(cell) );
                break;
            case STRING: //字符串
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case BOOLEAN: //Boolean
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;

            case BLANK: //空值
                cellValue = "";
                break;
            case ERROR: //故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }

    private static String formatNumber(Cell cell) {
        DecimalFormat df = new DecimalFormat("###.##");
        DecimalFormat dfPercent = new DecimalFormat("###.##%");
        if ( cell.getCellStyle().getDataFormatString().contains("%")) {
            return dfPercent.format(cell.getNumericCellValue());
        } else {
            return df.format(cell.getNumericCellValue());
        }

    }

    /**
     * 获取excel文件
     * @param filepath
     * @return
     */
    private static Workbook getWorkBook(String  filepath) {
        //创建Workbook工作薄对象，表示整个excel
        Workbook workbook = null;
        try {
            //获取excel文件的io流
            InputStream is = new FileInputStream(filepath);
            //根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
            if(filepath.endsWith(XLS)){
                //2003
                workbook = new HSSFWorkbook(is);
            }else if(filepath.endsWith(XLSX)){
                //2007
                workbook = new XSSFWorkbook(is);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return workbook;
    }

    /**
     * 检查文件
     * @param fileName
     * @throws IOException
     */
    private static void checkFile(String  fileName) throws IOException {
        //判断文件是否存在
        if(! FileUtils.existsFile(fileName)){
            throw new FileNotFoundException("文件不存在！");
        }

        //判断文件是否是excel文件
        if(!fileName.endsWith(XLS) && !fileName.endsWith(XLSX)){
            throw new IOException(fileName + "不是excel文件");
        }

    }

    /*public static void main(String[] args) {
        try {
            List<String[]> r = POIUtils.readExcel("C:\\Users\\Administrator\\Documents\\WeChat Files\\jsd546295512\\FileStorage\\File\\2024-08\\6694c84a1179a-金属报告20240715.xls",0);
            for (String[] s : r) {
                System.out.println(s[0]!=null?s[0].trim():"");
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }*/
}
